package com.foreknow.demo24;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/**
 * jdbc的工具类
 * 使用预处理实现
 */
public class DBUtils {
  private Connection connection;
  private PreparedStatement preparedStatement;
  private ResultSet rs;
  /**
   * 连接数据库的
   */
  public Connection getConnection() throws ClassNotFoundException, SQLException {
    Class.forName("com.mysql.jdbc.Driver");
    connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/demodb?useUnicode=true&characterEncoding=utf8&useSSL=false", "root", "zjjlive82");
    return connection;
  }

  /**
   * 查询
   * select * from emp where ename=?
   */
  public ResultSet queryInfo(String sql,Object...values) throws SQLException {
     preparedStatement = connection.prepareStatement(sql);
     //可以将SQL中的？替换为数组中的数据
    for(int i = 0;i<values.length;i++){
      preparedStatement.setObject(i+1,values[i]);
  }
    //执行查询
     rs = preparedStatement.executeQuery();
    return rs;
  }

  /**
   * insert update delete
   */
  public boolean updateInfo(String sql,Object...values) throws SQLException {
    preparedStatement = connection.prepareStatement(sql);
    //可以将SQL中的？替换为数组中的数据
    for(int i = 0;i<values.length;i++){
      preparedStatement.setObject(i+1,values[i]);
    }
    //执行DML操作
    int isRigth = preparedStatement.executeUpdate();
    return isRigth==1;
  }
  /**
   * 关闭资源的
   */
  public void close() throws SQLException {
    if(rs!=null) {
      rs.close();
    }
    if(preparedStatement!=null){
      preparedStatement.close();
    }
    if(connection!=null) {
      connection.close();
    }
  }

  public static void main(String[] args) {
    DBUtils dbUtils = new DBUtils();
    try {
      //1.调用连接数据的方法
      dbUtils.getConnection();
      //2.调用dml方法添加数据
      boolean isRigth = dbUtils.updateInfo("insert into emp(empno,ename,job) values(?,?,?)","8888","tom123","test");
      if(isRigth) {
        System.out.println("添加成功......");
      }else {
        System.out.println("添加失败......");
      }
      //3.调用查询的方法
      ResultSet rs = dbUtils.queryInfo("select * from emp");
      List<Emp> list = new ArrayList<>();
      while (rs.next()) {
        Emp emp = new Emp();
        emp.setEmpno(rs.getInt("empno"));
        emp.setEname(rs.getString("ename"));
        emp.setJob(rs.getString("job"));
        list.add(emp);
      }
      //遍历集合
      for (Emp emp : list) {
        System.out.println(emp.getEmpno() + "---" + emp.getEname() + "---" + emp.getJob());
      }
    } catch (SQLException e) {
      e.printStackTrace();
    } catch (ClassNotFoundException e) {
      e.printStackTrace();
    } finally {
      try {
        dbUtils.close();
      } catch (SQLException e) {
        e.printStackTrace();
      }
    }
  }


}
